﻿using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfApp.Models;
using System.IO;


namespace WpfApp.Services
{
    public static class DatabaseHelper
    {
        private static readonly string BaseDirectory = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..", "..", "..", "Data"));

        public static void Log(string message,string filename= "GeneralLog.txt")
        {
            string logFilePath = Path.Combine(BaseDirectory, "logs", filename);
            Directory.CreateDirectory(Path.GetDirectoryName(logFilePath)); // Ensure the directory exists
            using (StreamWriter writer = new StreamWriter(logFilePath, true))
            {
                writer.WriteLine($"{DateTime.Now}: {message}");
            }
        }

        public static SqliteConnection GetDatabaseConnection(string databaseFilePath,string operation="connect")
        {
            if (operation != "connect")
                Log($"尝试{operation}");
            try
            {
                if (!File.Exists(databaseFilePath))
                {
                    throw new FileNotFoundException($"Database file not found at path: {databaseFilePath}");
                }
                string connectionString = $"Data Source={databaseFilePath};Mode=ReadWriteCreate;";
                var connection = new SqliteConnection(connectionString);
                connection.Open();
                return connection;
            }
            catch (Exception ex)
            {
                Log($"Failed to connect to the database file, DatabaseFilePath: {databaseFilePath}");
                throw new Exception($"Error connecting to the database file: {databaseFilePath}", ex);
            }
        }
    }
    public class DatasetInitial
    {
        public static readonly string BaseDirectory = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..", "..", "..", "Data"));
        public static readonly string UserFileName = "UserManagement.db";
        public static readonly string WordFileName = "WordManagement.db";
        public static readonly string UserFilePath = Path.GetFullPath(Path.Combine(BaseDirectory, UserFileName));
        public static readonly string WordFilePath = Path.GetFullPath(Path.Combine(BaseDirectory, WordFileName));
        private static readonly string ConnectionString1 = $"Data Source={UserFilePath};Mode=ReadWriteCreate;";
        private static readonly string ConnectionString2 = $"Data Source={WordFilePath};Mode=ReadWriteCreate;";
        static bool IsDatabaseInitialized(string databaseFilePath)
        {
            return File.Exists(databaseFilePath);
        }


        static protected void InitialDataset()
        {
            // 检查 UserManagementDB 是否已初始化
            if (!IsDatabaseInitialized(UserFilePath))
            {
                DatabaseHelper.Log($"初始化{UserFileName}");
                CreateUserDatabase(ConnectionString1);
            }

            // 检查 WordManagementDB 是否已初始化
            if (!IsDatabaseInitialized(WordFilePath))
            {
                DatabaseHelper.Log($"初始化{WordFileName}");
                CreateWordDatabase(ConnectionString2);
            }
        }
        private static void CreateUserDatabase(string connectionString)
        {
            string createTablesQuery = @"
                CREATE TABLE IF NOT EXISTS User (
                    UserID INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Password TEXT NOT NULL,
                    CreatedTime DATETIME NOT NULL DEFAULT (datetime('now', 'localtime')),
                    MemoryLevel INTEGER DEFAULT 1
                );

                CREATE TABLE IF NOT EXISTS Wordbook (
                    WordbookID INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    CreatedTime DATETIME NOT NULL DEFAULT (datetime('now', 'localtime'))
                );

                CREATE TABLE IF NOT EXISTS User_Wordbook (
                    UserID INTEGER NOT NULL,
                    WordbookID INTEGER NOT NULL,
                    PRIMARY KEY (UserID, WordbookID),
                    FOREIGN KEY (UserID) REFERENCES User(UserID),
                    FOREIGN KEY (WordbookID) REFERENCES Wordbook(WordbookID)
                );

                CREATE TABLE IF NOT EXISTS LearningProgress (
                    ProgressID INTEGER PRIMARY KEY AUTOINCREMENT,
                    UserID INTEGER NOT NULL,
                    WordID INTEGER NOT NULL,
                    CorrectCount INTEGER DEFAULT 0,
                    LastReviewed DATETIME,
                    NextReview DATETIME,
                    ReviewCount INTEGER NOT NULL,
                    Weight REAL DEFAULT 1.0,
                    FOREIGN KEY (UserID) REFERENCES User(UserID)
                );
                ";

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SqliteCommand(createTablesQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
        }
        private static void CreateWordDatabase(string connectionString)
        {
            string createTablesQuery = @"
            CREATE TABLE IF NOT EXISTS Words (
                WordID INTEGER PRIMARY KEY AUTOINCREMENT,
                Word TEXT NOT NULL,
                Meaning TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS Wordbook_Words (
                WordbookID INTEGER NOT NULL,
                WordID INTEGER NOT NULL,
                PRIMARY KEY (WordbookID, WordID),
                FOREIGN KEY (WordbookID) REFERENCES Wordbook(WordbookID),
                FOREIGN KEY (WordID) REFERENCES Words(WordID)
            );
            ";

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SqliteCommand(createTablesQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
        }
    }

    }
